import pandas as pd
import numpy as np
from sklearn.preprocessing import MinMaxScaler
from utils.db import get_mysql_conn
import json
from datetime import datetime, timedelta

class DataProcessor:
    """数据预处理类，负责数据清洗、特征工程和数据归一化"""
    
    def __init__(self):
        self.mysql_conn = get_mysql_conn()
        self.user_features = {}  # 用户特征
        self.course_features = {}  # 课程特征
        self.user_course_matrix = {}  # 用户-课程矩阵
        
    def process_data(self):
        """处理数据的主函数"""
        print("开始数据预处理...")
        
        # 1. 加载原始数据
        user_behaviors = self._load_user_behaviors()
        courses = self._load_courses()
        
        if user_behaviors is None or courses is None:
            print("加载数据失败，无法继续处理")
            return False
            
        # 2. 数据清洗
        clean_behaviors = self._clean_data(user_behaviors)
        
        # 3. 特征工程
        self._extract_user_features(clean_behaviors)
        self._extract_course_features(courses, clean_behaviors)
        
        # 4. 构建用户-课程矩阵
        self._build_user_course_matrix(clean_behaviors)
        
        # 5. 归一化处理
        self._normalize_features()
        
        print("数据预处理完成")
        return True
        
    def _load_user_behaviors(self):
        """从数据库加载用户行为数据"""
        try:
            if not self.mysql_conn:
                self.mysql_conn = get_mysql_conn()
                
            query = """
            SELECT * FROM user_behaviors 
            WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
            """
            
            df = pd.read_sql(query, self.mysql_conn)
            print(f"加载了 {len(df)} 条用户行为数据")
            return df
            
        except Exception as e:
            print(f"加载用户行为数据失败: {e}")
            return None
    
    def _load_courses(self):
        """从数据库加载课程信息"""
        try:
            if not self.mysql_conn:
                self.mysql_conn = get_mysql_conn()
                
            query = "SELECT * FROM courses"
            
            df = pd.read_sql(query, self.mysql_conn)
            print(f"加载了 {len(df)} 条课程数据")
            return df
            
        except Exception as e:
            print(f"加载课程数据失败: {e}")
            return None
    
    def _clean_data(self, behaviors_df):
        """清洗数据，删除重复和异常数据"""
        print("正在清洗数据...")
        
        # 初始数据行数
        initial_rows = len(behaviors_df)
        
        # 1. 删除重复数据
        behaviors_df = behaviors_df.drop_duplicates()
        
        # 2. 删除空值
        behaviors_df = behaviors_df.dropna(subset=['user_id', 'course_id', 'event_type', 'timestamp'])
        
        # 3. 删除异常时间戳（未来时间）
        now = datetime.now()
        behaviors_df = behaviors_df[behaviors_df['timestamp'] <= now]
        
        # 4. 转换类型
        behaviors_df['user_id'] = behaviors_df['user_id'].astype(int)
        behaviors_df['course_id'] = behaviors_df['course_id'].astype(int)
        
        # 5. 解析extra_data列中的JSON数据
        def parse_extra_data(data):
            if isinstance(data, str):
                try:
                    return json.loads(data)
                except:
                    return {}
            return {}
            
        behaviors_df['extra_data_parsed'] = behaviors_df['extra_data'].apply(parse_extra_data)
        
        # 计算清洗掉的数据比例
        cleaned_rows = len(behaviors_df)
        removed_ratio = (initial_rows - cleaned_rows) / initial_rows if initial_rows > 0 else 0
        print(f"数据清洗完成。删除了 {initial_rows - cleaned_rows} 行数据 (比例: {removed_ratio:.2%})")
        
        return behaviors_df
    
    def _extract_user_features(self, behaviors_df):
        """提取用户特征"""
        print("正在提取用户特征...")
        
        # 获取所有用户ID
        user_ids = behaviors_df['user_id'].unique()
        
        # 计算当前时间，用于计算活跃度
        now = datetime.now()
        recent_days = 7  # 近期活跃度的天数
        recent_date = now - timedelta(days=recent_days)
        
        for user_id in user_ids:
            # 获取用户的所有行为
            user_behaviors = behaviors_df[behaviors_df['user_id'] == user_id]
            
            # 近期行为
            recent_behaviors = user_behaviors[user_behaviors['timestamp'] >= recent_date]
            
            # 计算各类行为数量
            views = len(user_behaviors[user_behaviors['event_type'] == 'view'])
            purchases = len(user_behaviors[user_behaviors['event_type'] == 'purchase'])
            favorites = len(user_behaviors[user_behaviors['event_type'] == 'favorite'])
            completes = len(user_behaviors[user_behaviors['event_type'] == 'complete'])
            
            # 计算近期行为数量
            recent_views = len(recent_behaviors[recent_behaviors['event_type'] == 'view'])
            recent_activities = len(recent_behaviors)
            
            # 计算活跃度
            total_activities = len(user_behaviors)
            activity_ratio = recent_activities / total_activities if total_activities > 0 else 0
            
            # 提取用户感兴趣的课程类别
            completed_courses = user_behaviors[user_behaviors['event_type'] == 'complete']['course_id'].unique()
            favorited_courses = user_behaviors[user_behaviors['event_type'] == 'favorite']['course_id'].unique()
            
            # 提取用户的平均评分
            ratings = []
            for _, row in user_behaviors[user_behaviors['event_type'] == 'rate'].iterrows():
                if 'rating' in row['extra_data_parsed']:
                    ratings.append(float(row['extra_data_parsed']['rating']))
            
            avg_rating = np.mean(ratings) if ratings else 0
            
            # 存储用户特征
            self.user_features[user_id] = {
                'total_views': views,
                'total_purchases': purchases, 
                'total_favorites': favorites,
                'total_completes': completes,
                'recent_views': recent_views,
                'activity_ratio': activity_ratio,
                'avg_rating': avg_rating,
                'completed_courses': list(completed_courses),
                'favorited_courses': list(favorited_courses)
            }
        
        print(f"已提取 {len(self.user_features)} 个用户的特征")
    
    def _extract_course_features(self, courses_df, behaviors_df):
        """提取课程特征"""
        print("正在提取课程特征...")
        
        # 获取所有课程ID
        course_ids = courses_df['id'].unique()
        
        for course_id in course_ids:
            # 获取课程信息
            course_info = courses_df[courses_df['id'] == course_id].iloc[0]
            
            # 获取课程的所有行为
            course_behaviors = behaviors_df[behaviors_df['course_id'] == course_id]
            
            # 计算各类行为数量
            views = len(course_behaviors[course_behaviors['event_type'] == 'view'])
            purchases = len(course_behaviors[course_behaviors['event_type'] == 'purchase'])
            favorites = len(course_behaviors[course_behaviors['event_type'] == 'favorite'])
            completes = len(course_behaviors[course_behaviors['event_type'] == 'complete'])
            
            # 提取评分信息
            ratings = []
            for _, row in course_behaviors[course_behaviors['event_type'] == 'rate'].iterrows():
                if 'rating' in row['extra_data_parsed']:
                    ratings.append(float(row['extra_data_parsed']['rating']))
            
            avg_rating = np.mean(ratings) if ratings else 0
            rating_count = len(ratings)
            
            # 提取课程类别和标签
            category = course_info['category'] if 'category' in course_info else ''
            tags = course_info['tags'] if 'tags' in course_info else ''
            difficulty = course_info['difficulty'] if 'difficulty' in course_info else 'medium'
            
            # 计算热度
            heat = 0.3 * views + 0.5 * purchases + 0.2 * favorites
            
            # 存储课程特征
            self.course_features[course_id] = {
                'views': views,
                'purchases': purchases,
                'favorites': favorites,
                'completes': completes,
                'avg_rating': avg_rating,
                'rating_count': rating_count,
                'category': category,
                'tags': tags,
                'difficulty': difficulty,
                'heat': heat
            }
        
        print(f"已提取 {len(self.course_features)} 个课程的特征")
    
    def _build_user_course_matrix(self, behaviors_df):
        """构建用户-课程矩阵"""
        print("正在构建用户-课程矩阵...")
        
        # 获取用户评分数据
        rating_behaviors = behaviors_df[behaviors_df['event_type'] == 'rate']
        
        for _, row in rating_behaviors.iterrows():
            user_id = row['user_id']
            course_id = row['course_id']
            
            # 获取评分
            rating = 0
            if 'rating' in row['extra_data_parsed']:
                rating = float(row['extra_data_parsed']['rating'])
            
            # 更新用户-课程矩阵
            if user_id not in self.user_course_matrix:
                self.user_course_matrix[user_id] = {}
            
            self.user_course_matrix[user_id][course_id] = rating
        
        # 处理其他行为类型，为没有评分的记录生成隐式评分
        non_rating_behaviors = behaviors_df[behaviors_df['event_type'] != 'rate']
        
        # 行为类型的权重
        behavior_weights = {
            'complete': 5.0,  # 完成课程视为最高评分
            'purchase': 4.0,  # 购买课程视为较高评分
            'favorite': 3.5,  # 收藏课程视为中高评分
            'view': 2.0       # 浏览课程视为中评分
        }
        
        for _, row in non_rating_behaviors.iterrows():
            user_id = row['user_id']
            course_id = row['course_id']
            event_type = row['event_type']
            
            # 如果用户已经对这个课程有评分，跳过
            if user_id in self.user_course_matrix and course_id in self.user_course_matrix[user_id]:
                continue
            
            # 根据行为类型分配隐式评分
            if event_type in behavior_weights:
                if user_id not in self.user_course_matrix:
                    self.user_course_matrix[user_id] = {}
                
                # 只记录最高级别的行为作为隐式评分
                current_score = self.user_course_matrix[user_id].get(course_id, 0)
                self.user_course_matrix[user_id][course_id] = max(current_score, behavior_weights[event_type])
        
        print(f"已构建用户-课程矩阵，包含 {len(self.user_course_matrix)} 个用户")
    
    def _normalize_features(self):
        """归一化特征"""
        print("正在归一化特征...")
        
        # 归一化用户特征中的数值特征
        user_numeric_features = [
            'total_views', 'total_purchases', 'total_favorites', 'total_completes',
            'recent_views', 'activity_ratio', 'avg_rating'
        ]
        
        # 提取所有用户的数值特征
        user_values = []
        for user_id, features in self.user_features.items():
            user_values.append([features.get(f, 0) for f in user_numeric_features])
        
        # 使用MinMaxScaler归一化
        if user_values:
            scaler = MinMaxScaler()
            normalized_values = scaler.fit_transform(user_values)
            
            # 更新用户特征
            for i, user_id in enumerate(self.user_features.keys()):
                for j, feature_name in enumerate(user_numeric_features):
                    self.user_features[user_id][feature_name] = normalized_values[i, j]
        
        # 归一化课程特征中的数值特征
        course_numeric_features = [
            'views', 'purchases', 'favorites', 'completes',
            'avg_rating', 'rating_count', 'heat'
        ]
        
        # 提取所有课程的数值特征
        course_values = []
        for course_id, features in self.course_features.items():
            course_values.append([features.get(f, 0) for f in course_numeric_features])
        
        # 使用MinMaxScaler归一化
        if course_values:
            scaler = MinMaxScaler()
            normalized_values = scaler.fit_transform(course_values)
            
            # 更新课程特征
            for i, course_id in enumerate(self.course_features.keys()):
                for j, feature_name in enumerate(course_numeric_features):
                    self.course_features[course_id][feature_name] = normalized_values[i, j]
        
        print("特征归一化完成")
    
    def get_user_features(self):
        """获取用户特征"""
        return self.user_features
    
    def get_course_features(self):
        """获取课程特征"""
        return self.course_features
    
    def get_user_course_matrix(self):
        """获取用户-课程矩阵"""
        return self.user_course_matrix

# 测试代码
if __name__ == "__main__":
    processor = DataProcessor()
    processor.process_data()
    
    # 打印一些统计信息
    user_features = processor.get_user_features()
    course_features = processor.get_course_features()
    user_course_matrix = processor.get_user_course_matrix()
    
    print(f"用户特征数量: {len(user_features)}")
    print(f"课程特征数量: {len(course_features)}")
    print(f"用户-课程矩阵用户数: {len(user_course_matrix)}")
    
    # 打印第一个用户的特征
    if user_features:
        first_user_id = list(user_features.keys())[0]
        print(f"\n用户 {first_user_id} 的特征:")
        print(json.dumps(user_features[first_user_id], indent=2))
    
    # 打印第一个课程的特征
    if course_features:
        first_course_id = list(course_features.keys())[0]
        print(f"\n课程 {first_course_id} 的特征:")
        print(json.dumps(course_features[first_course_id], indent=2)) 